1 Imports CrystalDecisions.CrystalReports.Engine
2 Imports CrystalDecisions.Shared
3 Imports System.Data.OleDb
4 Imports System.Data
5 Public Class FrmREPORTSDated
6 Private Sub FrmREPORTSDated_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
7 'Dim ParamCollection As New CrystalDecisions.Shared.ParameterValues
8 mReport = New ReportDocument()
9 Me.MdiParent = MDIMain
10 CrystalReportViewer1.Height = (Me.Height - Panel1.Height) + 10
11 CrystalReportViewer1.Width = Me.Width - 10
12 xRefresh()
13 End Sub
14
15 Private Sub dtfrom_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dtfrom.ValueChanged
16 Call xRefresh()
17 End Sub
18
19 Private Sub dtto_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dtto.ValueChanged
20 Call xRefresh()
21 End Sub
22
23 Private Function xRefresh()
24 Dim ParamCollection As New CrystalDecisions.Shared.ParameterValues
25 With MDIMain
26 i_Print = 0
27 .P_Print.Visible = True
28 .tmr_Print.Enabled = True
29 Select Case UCase(globalFRM)
30 Case UCase("FrmPURCHASEORDER")
31 Rpt_SqlStr = "SELECT * FROM TBL_Purchase_Order WHERE Purchased_Date >='" & Format(dtfrom.Value, "MM/dd/yyyy") & "' AND Purchased_Date <='" & Format(dtto.Value, "MM/dd/yyyy") & "' AND Approved ='Yes'"
32 mReport.Load(Application.StartupPath & "\ReportX\PurchaseOrder_RPT.rpt")
33 Call DataSourceConnection_Report()
34 mReport.SetDataSource(ExecuteSQLQuery(Rpt_SqlStr))
35
36 ParamCollection.Add(_USER)
37 mReport.DataDefinition.ParameterFields("user").ApplyCurrentValues(ParamCollection)
38
39 ParamCollection.Add(ParamCompanyName)
40 mReport.DataDefinition.ParameterFields("CompName").ApplyCurrentValues(ParamCollection)
41
42 ParamCollection.Add(ParamCompanyLoc)
43 mReport.DataDefinition.ParameterFields("CompLoc").ApplyCurrentValues(ParamCollection)
44
45 ParamCollection.Add(ParamCompanyContact)
46 mReport.DataDefinition.ParameterFields("CompContact").ApplyCurrentValues(ParamCollection)
47
48 CrystalReportViewer1.ReportSource = mReport
49 'CrystalReportViewer1.RefreshReport()
50 Case UCase("frmsales_collection")
51 Rpt_SqlStr = "SELECT * FROM TBL_Sales_Receipt WHERE Sales_ID NOT IN (SELECT Sales_ID FROM TBL_Sales_Void) " & _
52 "AND Receipt_Date >='" & Format(dtfrom.Value, "MM/dd/yyyy") & "' AND Receipt_Date <='" & Format(dtto.Value, "MM/dd/yyyy") & "'"
53 mReport.Load(Application.StartupPath & "\ReportX\SALES_REPORT.rpt")
54 ParamDVFrom.Value = dtfrom.Value
55 ParamDVTo.Value = dtto.Value
56
57 Call DataSourceConnection_Report()
58 mReport.SetDataSource(ExecuteSQLQuery(Rpt_SqlStr))
59
60 ParamCollection.Add(ParamDVFrom)
61 mReport.DataDefinition.ParameterFields("txtfrom").ApplyCurrentValues(ParamCollection)
62
63 ParamCollection.Add(ParamDVTo)
64 mReport.DataDefinition.ParameterFields("txtto").ApplyCurrentValues(ParamCollection)
65
66 ParamCollection.Add(_USER)
67 mReport.DataDefinition.ParameterFields("user").ApplyCurrentValues(ParamCollection)
68
69 ParamCollection.Add(ParamCompanyName)
70 mReport.DataDefinition.ParameterFields("CompName").ApplyCurrentValues(ParamCollection)
71
72 ParamCollection.Add(ParamCompanyLoc)
73 mReport.DataDefinition.ParameterFields("CompLoc").ApplyCurrentValues(ParamCollection)
74
75 CrystalReportViewer1.ReportSource = mReport
76
77 Audit_Trail(xUser_ID, TimeOfDay, "Print Report - Sales Collection ")
78
79 Case UCase("frmcollection_void")
80 Rpt_SqlStr = "SELECT * FROM TBL_Sales_Sold_Detail WHERE Sales_ID IN " & _
81 "(SELECT Sales_ID FROM TBL_Sales_Sold WHERE Sales_Date >='" & Format(dtfrom.Value, "MM/dd/yyyy") & "' AND Sales_Date <='" & Format(dtto.Value, "MM/dd/yyyy") & "')"
82 mReport.Load(Application.StartupPath & "\ReportX\SALES_REPORT_VOID.rpt")
83
84 Call DataSourceConnection_Report()
85 mReport.SetDataSource(ExecuteSQLQuery(Rpt_SqlStr))
86 ParamCollection.Add(_USER)
87 mReport.DataDefinition.ParameterFields("user").ApplyCurrentValues(ParamCollection)
88
89 ParamCollection.Add(ParamCompanyName)
90 mReport.DataDefinition.ParameterFields("CompName").ApplyCurrentValues(ParamCollection)
91
92 ParamCollection.Add(ParamCompanyLoc)
93 mReport.DataDefinition.ParameterFields("CompLoc").ApplyCurrentValues(ParamCollection)
94
95 CrystalReportViewer1.ReportSource = mReport
96 Audit_Trail(xUser_ID, TimeOfDay, "Print Report - Sales Collection Void ")
97
98 Case UCase("frmcollection_summary")
99 Rpt_SqlStr = "SELECT * FROM TBL_Sales_Receipt WHERE Sales_ID NOT IN (SELECT Sales_ID FROM TBL_Sales_Void) " & _
100 "AND Receipt_Date >='" & Format(dtfrom.Value, "MM/dd/yyyy") & "' AND Receipt_Date <='" & Format(dtto.Value, "MM/dd/yyyy") & "'"
101 mReport.Load(Application.StartupPath & "\ReportX\Collection_Summary.rpt")
102 Call DataSourceConnection_Report()
103
104 mReport.SetDataSource(ExecuteSQLQuery(Rpt_SqlStr))
105 ParamCollection.Add(_USER)
106 mReport.DataDefinition.ParameterFields("user").ApplyCurrentValues(ParamCollection)
107
108 ParamCollection.Add(ParamCompanyName)
109 mReport.DataDefinition.ParameterFields("CompName").ApplyCurrentValues(ParamCollection)
110
111 ParamCollection.Add(ParamCompanyLoc)
112 mReport.DataDefinition.ParameterFields("CompLoc").ApplyCurrentValues(ParamCollection)
113
114 CrystalReportViewer1.ReportSource = mReport
115 Audit_Trail(xUser_ID, TimeOfDay, "Print Report - Collection Summary ")
116
117 Case UCase("frmsales_report_receipt")
118 Rpt_SqlStr = "SELECT * FROM TBL_Sales_Receipt " & _
119 "WHERE Sales_ID NOT IN (SELECT Sales_ID FROM TBL_Sales_Void) " & _
120 "AND Receipt_Date >=' " & Format(dtfrom.Value, "MM/dd/yyyy") & _
121 " ' AND Receipt_Date <=' " & Format(dtto.Value, "MM/dd/yyyy") & "'"
122 mReport.Load(Application.StartupPath & "\ReportX\SALES_RECEIPT_REPORT.rpt")
123
124 Call DataSourceConnection_Report()
125 mReport.SetDataSource(ExecuteSQLQuery(Rpt_SqlStr))
126
127 ParamCollection.Add(_USER)
128 mReport.DataDefinition.ParameterFields("user").ApplyCurrentValues(ParamCollection)
129
130 ParamCollection.Add(ParamCompanyName)
131 mReport.DataDefinition.ParameterFields("CompName").ApplyCurrentValues(ParamCollection)
132
133 ParamCollection.Add(ParamCompanyLoc)
134 mReport.DataDefinition.ParameterFields("CompLoc").ApplyCurrentValues(ParamCollection)
135
136 CrystalReportViewer1.ReportSource = mReport
137 Audit_Trail(xUser_ID, TimeOfDay, "Print Report - Sales Report Receipt ")
138 Case UCase("frmproduct_pacing_fast_moving")
139 Rpt_SqlStr = "SELECT DISTINCT TBL_Category_Item_File.Item_ID, TBL_Sales_Sold_Detail.Item_Name, TBL_Sales_Sold_Detail.Item_QTY, SUM(Item_QTY) " & _
140 "FROM TBL_Sales_Sold_Detail " & _
141 "INNER JOIN TBL_Category_Item_File ON TBL_Sales_Sold_Detail.Item_ID = TBL_Category_Item_File.Item_ID " & _
142 "INNER JOIN TBL_Sales_Sold ON TBL_Sales_Sold_Detail.Sales_ID = TBL_Sales_Sold.Sales_ID " & _
143 "WHERE TBL_Sales_Sold.Sales_Date >='" & Format(dtfrom.Value, "MM/dd/yyyy") & "' AND TBL_Sales_Sold.Sales_Date <='" & Format(dtto.Value, "MM/dd/yyyy") & "'" & _
144 "GROUP BY TBL_Category_Item_File.Item_ID, TBL_Sales_Sold_Detail.Item_Name, TBL_Sales_Sold_Detail.Item_QTY "
145 'mReport.Load("D:\_PROGRAM\_VB.NET\Sales and Inventory\prjSalesInventory\prjSalesInventory\ReportX\ProductPacing_Report.rpt")
146 mReport.Load(Application.StartupPath & "\ReportX\ProductPacing_Report.rpt")
147 Call DataSourceConnection_Report()
148 mReport.SetDataSource(ExecuteSQLQuery(Rpt_SqlStr))
149
150 ParamCollection.Add(_USER)
151 mReport.DataDefinition.ParameterFields("user").ApplyCurrentValues(ParamCollection)
152
153 ParamCollection.Add(ParamCompanyName)
154 mReport.DataDefinition.ParameterFields("CompName").ApplyCurrentValues(ParamCollection)
155
156 ParamCollection.Add(ParamCompanyLoc)
157 mReport.DataDefinition.ParameterFields("CompLoc").ApplyCurrentValues(ParamCollection)
158
159 CrystalReportViewer1.ReportSource = mReport
160 Me.MdiParent = MDIMain
161 Case UCase("frmproduct_pacing_slow_moving")
162 Rpt_SqlStr = "SELECT * FROM TBL_Category_Item_File " & _
163 "WHERE Item_ID NOT IN " & _
164 "(SELECT DISTINCT Item_ID FROM TBL_Sales_Sold_Detail " & _
165 " INNER JOIN TBL_Sales_Sold ON TBL_Sales_Sold_Detail.Sales_ID = TBL_Sales_Sold_Detail.Sales_ID " & _
166 " WHERE TBL_Sales_Sold.Sales_Date >='" & Format(dtfrom.Value, "MM/dd/yyyy") & "' AND TBL_Sales_Sold.Sales_Date <='" & Format(dtto.Value, "MM/dd/yyyy") & "')"
167 mReport.Load(Application.StartupPath & "\ReportX\Product_pacing_slow_moving.rpt")
168 Call DataSourceConnection_Report()
169 mReport.SetDataSource(ExecuteSQLQuery(Rpt_SqlStr))
170
171 ParamCollection.Add(_USER)
172 mReport.DataDefinition.ParameterFields("user").ApplyCurrentValues(ParamCollection)
173
174 ParamCollection.Add(ParamCompanyName)
175 mReport.DataDefinition.ParameterFields("CompName").ApplyCurrentValues(ParamCollection)
176
177 ParamCollection.Add(ParamCompanyLoc)
178 mReport.DataDefinition.ParameterFields("CompLoc").ApplyCurrentValues(ParamCollection)
179
180 CrystalReportViewer1.ReportSource = mReport
181 Me.MdiParent = MDIMain
182 Case UCase("frmpurchaseorder_receive")
183 Rpt_SqlStr = "SELECT * FROM TBL_Purchase_Order WHERE Purchased_Date >='" & Format(dtfrom.Value, "MM/dd/yyyy") & "' AND Purchased_Date <='" & Format(dtto.Value, "MM/dd/yyyy") & "' AND Approved ='Yes'"
184
185 mReport.Load(Application.StartupPath & "\ReportX\PurchaseReceive_RPT.rpt")
186 Call DataSourceConnection_Report()
187 mReport.SetDataSource(ExecuteSQLQuery(Rpt_SqlStr))
188
189 ParamCollection.Add(_USER)
190 mReport.DataDefinition.ParameterFields("user").ApplyCurrentValues(ParamCollection)
191
192 ParamCollection.Add(ParamCompanyName)
193 mReport.DataDefinition.ParameterFields("CompName").ApplyCurrentValues(ParamCollection)
194
195 ParamCollection.Add(ParamCompanyLoc)
196 mReport.DataDefinition.ParameterFields("CompLoc").ApplyCurrentValues(ParamCollection)
197
198 ParamCollection.Add(ParamCompanyContact)
199 mReport.DataDefinition.ParameterFields("CompContact").ApplyCurrentValues(ParamCollection)
200
201 CrystalReportViewer1.ReportSource = mReport
202 Me.MdiParent = MDIMain
203
204 End Select
205 .P_Print.Visible = False
206 .tmr_Print.Enabled = False
207 End With
208 xRefresh = 0
209 End Function
210 End Class
